/*
 Navicat Premium Data Transfer

 Source Server         : hnbxkj.tpddns.cn
 Source Server Type    : MySQL
 Source Server Version : 80034 (8.0.34)
 Source Host           : hnbxkj.tpddns.cn:3307
 Source Schema         : TengCe_data

 Target Server Type    : MySQL
 Target Server Version : 80034 (8.0.34)
 File Encoding         : 65001

 Date: 15/04/2025 22:13:10
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for chat_department
-- ----------------------------
DROP TABLE IF EXISTS `chat_department`;
CREATE TABLE `chat_department` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '部门名称',
  `parent_id` bigint DEFAULT NULL COMMENT '父部门ID',
  `level` int NOT NULL COMMENT '部门层级',
  `leader_id` bigint DEFAULT NULL COMMENT '部门负责人ID',
  `status` tinyint DEFAULT '1' COMMENT '状态(0:禁用,1:启用)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门信息';

-- ----------------------------
-- Records of chat_department
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for chat_group
-- ----------------------------
DROP TABLE IF EXISTS `chat_group`;
CREATE TABLE `chat_group` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '群组名称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '群组头像',
  `creator_id` bigint NOT NULL COMMENT '创建者ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态(0:已解散,1:正常)',
  `dissolve_time` datetime DEFAULT NULL COMMENT '解散时间',
  `dissolve_by` bigint DEFAULT NULL COMMENT '解散操作人ID',
  `type` tinyint NOT NULL DEFAULT '0' COMMENT '群组类型(0:普通群,1:部门群,2:项目群)',
  `description` varchar(255) DEFAULT NULL COMMENT '群组描述',
  `real_name` varchar(255) DEFAULT NULL COMMENT '真实姓名',
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_creator` (`creator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='聊天群组';

-- ----------------------------
-- Records of chat_group
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for chat_group_announcement
-- ----------------------------
DROP TABLE IF EXISTS `chat_group_announcement`;
CREATE TABLE `chat_group_announcement` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_id` bigint NOT NULL COMMENT '群组ID',
  `creator_id` bigint NOT NULL COMMENT '创建者ID',
  `title` varchar(100) NOT NULL COMMENT '公告标题',
  `content` text NOT NULL COMMENT '公告内容',
  `is_top` tinyint(1) DEFAULT '0' COMMENT '是否置顶(0:否,1:是)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` tinyint DEFAULT '1' COMMENT '状态(0:已删除,1:正常)',
  PRIMARY KEY (`id`),
  KEY `idx_group` (`group_id`),
  KEY `idx_creator` (`creator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='群公告';

-- ----------------------------
-- Records of chat_group_announcement
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for chat_group_member
-- ----------------------------
DROP TABLE IF EXISTS `chat_group_member`;
CREATE TABLE `chat_group_member` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_id` bigint NOT NULL COMMENT '群组ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `role` tinyint DEFAULT '0' COMMENT '角色(0:普通成员,1:管理员,2:群主)',
  `join_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
  `join_type` tinyint DEFAULT '0' COMMENT '加入方式(0:邀请加入,1:申请加入,2:直接加入)',
  `inviter_id` bigint DEFAULT NULL COMMENT '邀请人ID',
  `status` tinyint DEFAULT '1' COMMENT '状态(0:已退出,1:正常)',
  `leave_time` datetime DEFAULT NULL COMMENT '退出时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_group_user` (`group_id`,`user_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_group_status` (`group_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='群组成员';

-- ----------------------------
-- Records of chat_group_member
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for chat_group_owner_change
-- ----------------------------
DROP TABLE IF EXISTS `chat_group_owner_change`;
CREATE TABLE `chat_group_owner_change` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_id` bigint NOT NULL COMMENT '群组ID',
  `previous_owner_id` bigint NOT NULL COMMENT '原群主ID',
  `new_owner_id` bigint NOT NULL COMMENT '新群主ID',
  `change_type` varchar(20) NOT NULL COMMENT '变更类型(quit:退群,transfer:转让,auto:自动继承)',
  `change_reason` varchar(255) DEFAULT NULL COMMENT '变更原因',
  `operator_id` bigint DEFAULT NULL COMMENT '操作人ID(系统操作则为NULL)',
  `change_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '变更时间',
  PRIMARY KEY (`id`),
  KEY `idx_group` (`group_id`),
  KEY `idx_change_time` (`change_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='群主变更记录';

-- ----------------------------
-- Records of chat_group_owner_change
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for chat_message
-- ----------------------------
DROP TABLE IF EXISTS `chat_message`;
CREATE TABLE `chat_message` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sender_id` bigint NOT NULL COMMENT '发送者ID',
  `receiver_id` bigint DEFAULT NULL COMMENT '接收者ID(私聊)',
  `group_id` bigint DEFAULT NULL COMMENT '群组ID(群聊)',
  `content` text COMMENT '消息内容',
  `content_type` varchar(20) NOT NULL DEFAULT 'text' COMMENT '内容类型(text,image,file,video,audio)',
  `file_url` varchar(255) DEFAULT NULL COMMENT '文件URL',
  `file_name` varchar(255) DEFAULT NULL COMMENT '文件名',
  `file_size` bigint DEFAULT NULL COMMENT '文件大小(字节)',
  `file_duration` int DEFAULT NULL COMMENT '音视频时长)',
  `is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读(0:未读,1:已读)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `status` tinyint DEFAULT '1' COMMENT '状态(0:已删除,1:正常)',
  `deleted_by` bigint DEFAULT NULL COMMENT '删除人ID',
  `deleted_time` datetime DEFAULT NULL COMMENT '删除时间',
  `message_type` tinyint NOT NULL DEFAULT '0' COMMENT '消息类型(0:普通消息,1:系统消息,2:通知消息)',
  PRIMARY KEY (`id`),
  KEY `idx_sender` (`sender_id`),
  KEY `idx_receiver` (`receiver_id`),
  KEY `idx_group` (`group_id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='聊天消息';

-- ----------------------------
-- Records of chat_message
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for chat_user_session
-- ----------------------------
DROP TABLE IF EXISTS `chat_user_session`;
CREATE TABLE `chat_user_session` (
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `session_id` varchar(100) NOT NULL COMMENT '会话ID',
  `last_online_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '最后在线时间',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户会话';

-- ----------------------------
-- Records of chat_user_session
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for commo_area
-- ----------------------------
DROP TABLE IF EXISTS `commo_area`;
CREATE TABLE `commo_area` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '区划信息id',
  `pid` int DEFAULT NULL COMMENT '父级挂接id',
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区划编码',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区划名称',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` tinyint(1) DEFAULT NULL COMMENT '状态 0 正常 -2 删除 -1 停用',
  `level` tinyint(1) DEFAULT NULL COMMENT '级次id 0:省/自治区/直辖市 1:市级 2:县级',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='地址区域表';

-- ----------------------------
-- Records of commo_area
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for company_client_companies
-- ----------------------------
DROP TABLE IF EXISTS `company_client_companies`;
CREATE TABLE `company_client_companies` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '公司ID',
  `company_name` varchar(100) NOT NULL COMMENT '甲方公司名称',
  `company_location` varchar(100) DEFAULT NULL COMMENT '甲方公司所在地',
  `contact_person` varchar(50) DEFAULT NULL COMMENT '甲方对接人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '对接人联系电话',
  `contact_email` varchar(100) DEFAULT NULL COMMENT '对接人电子邮箱',
  `tax_identification_number` varchar(50) DEFAULT NULL COMMENT '纳税人识别号',
  `bank_account` varchar(50) DEFAULT NULL COMMENT '银行账号',
  `bank_name` varchar(100) DEFAULT NULL COMMENT '开户银行',
  `company_remark` text COMMENT '公司备注',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_company_name` (`company_name`) COMMENT '公司名称唯一索引',
  KEY `idx_company_location` (`company_location`) COMMENT '公司所在地索引',
  KEY `idx_contact_person` (`contact_person`) COMMENT '对接人索引'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='甲方公司信息表';

-- ----------------------------
-- Records of company_client_companies
-- ----------------------------
BEGIN;
INSERT INTO `company_client_companies` (`id`, `company_name`, `company_location`, `contact_person`, `contact_phone`, `contact_email`, `tax_identification_number`, `bank_account`, `bank_name`, `company_remark`, `created_at`, `updated_at`) VALUES (1, '北京科技有限公司', '北京市海淀区中关村南大街5号', '张经理', '13800138001', 'zhang@tech.com', '91110101MA12345678', '6222020100001234567', '中国工商银行北京海淀支行', '长期合作客户，付款及时', '2025-04-13 12:40:44', '2025-04-13 12:40:44');
INSERT INTO `company_client_companies` (`id`, `company_name`, `company_location`, `contact_person`, `contact_phone`, `contact_email`, `tax_identification_number`, `bank_account`, `bank_name`, `company_remark`, `created_at`, `updated_at`) VALUES (2, '北京小米移动软件有限公司', '北京市海淀区西二旗中路33号院小米科技园', '王翔', '010-60606688', 'business@xia', '91110108592345678R', '0200000619234567891', '招商银行北京分行', '小米集团旗下移动软件业务主体', '2025-04-13 12:41:57', '2025-04-13 12:41:57');
COMMIT;

-- ----------------------------
-- Table structure for employee_work_statistics
-- ----------------------------
DROP TABLE IF EXISTS `employee_work_statistics`;
CREATE TABLE `employee_work_statistics` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `work_date` date NOT NULL COMMENT '日期',
  `base_type` enum('内部','外部公司') NOT NULL COMMENT '基地类型',
  `base_name` varchar(50) NOT NULL COMMENT '基地名称',
  `client_company` varchar(100) NOT NULL COMMENT '甲方公司名称',
  `project_id` varchar(50) NOT NULL COMMENT '项目ID',
  `project_name` varchar(100) NOT NULL COMMENT '项目名称',
  `task_id` varchar(50) NOT NULL COMMENT '任务ID',
  `account_name` varchar(50) NOT NULL COMMENT '账号名',
  `project_unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '项目单位(包/帧)',
  `projectload` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '项目工作量',
  `work_unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '最小数据单位(框/线/区域等)',
  `workload` decimal(10,2) NOT NULL COMMENT '最小数据单位工作量',
  `data_status` enum('草稿','待处理','处理中','已完成','已验收','已驳回') DEFAULT '草稿' COMMENT '数据状态',
  `rejection_count` int DEFAULT '0' COMMENT '驳回次数',
  `submission_status` enum('已提交','待审核','已驳回','已确认') DEFAULT '已提交' COMMENT '提交状态',
  `submission_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '提交日期(自动生成)',
  `delivery_date` date DEFAULT NULL COMMENT '交付日期',
  `submitter_name` varchar(50) NOT NULL COMMENT '提交人姓名',
  `is_locked` tinyint(1) DEFAULT '0' COMMENT '是否锁定(0-未锁定,1-已锁定)',
  `remarks` text COMMENT '备注',
  `additional_notes` text COMMENT '补充备注',
  PRIMARY KEY (`id`),
  KEY `idx_work_date` (`work_date`),
  KEY `idx_base_info` (`base_type`,`base_name`),
  KEY `idx_client_company` (`client_company`),
  KEY `idx_project_info` (`project_id`,`project_name`),
  KEY `idx_employee` (`account_name`),
  KEY `idx_task` (`task_id`),
  KEY `idx_status` (`submission_status`),
  KEY `idx_data_status` (`data_status`),
  KEY `idx_submitter` (`submitter_name`),
  KEY `idx_delivery_date` (`delivery_date`),
  CONSTRAINT `employee_work_statistics_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects_all` (`project_id`),
  CONSTRAINT `employee_work_statistics_ibfk_2` FOREIGN KEY (`account_name`) REFERENCES `user_accounts` (`account_name`),
  CONSTRAINT `employee_work_statistics_ibfk_3` FOREIGN KEY (`client_company`) REFERENCES `company_client_companies` (`company_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工工作量统计表';

-- ----------------------------
-- Records of employee_work_statistics
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for notices
-- ----------------------------
DROP TABLE IF EXISTS `notices`;
CREATE TABLE `notices` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '公告ID',
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告标题',
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告内容',
  `notice_type` enum('公司公告','部门通知','项目通报','系统消息') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告类型',
  `company_wide` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否所有公司可见',
  `base_type` enum('内部','外部公司') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '基地类型(当company_wide为false时有效)',
  `base_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '基地名称(当company_wide为false时有效)',
  `department` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '部门名称',
  `publisher_id` bigint NOT NULL COMMENT '发布人ID',
  `publisher_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '发布人姓名',
  `publisher_role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '发布人角色',
  `publish_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `start_time` datetime NOT NULL COMMENT '生效时间',
  `end_time` datetime DEFAULT NULL COMMENT '截止时间(为空表示长期有效)',
  `status` enum('草稿','已发布','已撤回','已过期') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '已发布',
  `priority` tinyint DEFAULT '0' COMMENT '优先级(0-普通,1-重要,2-紧急)',
  `view_count` int DEFAULT '0' COMMENT '查看次数',
  PRIMARY KEY (`id`),
  KEY `idx_notice_type` (`notice_type`),
  KEY `idx_visibility` (`company_wide`,`base_type`,`base_name`),
  KEY `idx_time_range` (`start_time`,`end_time`),
  KEY `idx_status` (`status`),
  KEY `idx_publisher` (`publisher_id`),
  KEY `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公司公告信息表';

-- ----------------------------
-- Records of notices
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for online_monitoring
-- ----------------------------
DROP TABLE IF EXISTS `online_monitoring`;
CREATE TABLE `online_monitoring` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `randomNumber` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '随机数唯一值',
  `base_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '基地名称',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
  `real_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '真实姓名',
  `prov` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '登录省份',
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '登录地市',
  `ipNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'ip地址',
  `lsp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'ip网路信息',
  `onOffLine` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'true' COMMENT '是否在线',
  `webJSONToken` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '登录token',
  `loginTime` datetime DEFAULT NULL COMMENT '登录时间',
  `leaveTime` datetime DEFAULT NULL COMMENT '离线时间',
  `numberOfTime` int DEFAULT NULL COMMENT '当日登录次数',
  `deviceLoginNum` int DEFAULT NULL COMMENT '允许多设备登录数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=38393 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='socket 在线数据';

-- ----------------------------
-- Records of online_monitoring
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for permissions
-- ----------------------------
DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `base_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '基地类型',
  `base_name` varchar(50) NOT NULL COMMENT '基地名称',
  `base_abbr_name` varchar(50) NOT NULL COMMENT '基地简称',
  `base_location` varchar(255) NOT NULL COMMENT '基地所在地',
  `permission_name` varchar(50) NOT NULL COMMENT '权限名称',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator_name` varchar(50) NOT NULL COMMENT '创建者姓名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_permission` (`base_type`,`base_name`,`permission_name`) COMMENT '防止重复权限',
  KEY `idx_base_type` (`base_type`),
  KEY `idx_base_name` (`base_name`),
  KEY `idx_permission_name` (`permission_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统权限表';

-- ----------------------------
-- Records of permissions
-- ----------------------------
BEGIN;
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (1, '内部', '北京基地', '', '', '管理员', '2025-04-13 10:50:18', '张三');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (2, '内部', '上海基地', '', '', '项目经理', '2025-04-13 10:50:18', '王五');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (3, '外部公司', '广州基地', '', '', '项目主管', '2025-04-13 10:50:18', '王客服');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (4, '内部', '北京基地', '', '', '项目经理', '2025-04-13 10:50:18', '王二');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (5, '内部', '北京基地', '', '', '财务', '2025-04-13 10:53:30', '里斯');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (6, '系统', '北京基地', '', '', '聊天机器人', '2025-04-13 10:57:08', '管理员');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (7, '内部', '北京基地', '', '', '标注员工', '2025-04-13 11:00:00', '管理员');
INSERT INTO `permissions` (`id`, `base_type`, `base_name`, `base_abbr_name`, `base_location`, `permission_name`, `created_at`, `creator_name`) VALUES (8, '外部', '外部基地', '', '', '游客', '2025-04-13 11:00:38', '管理员');
COMMIT;

-- ----------------------------
-- Table structure for permissions_base
-- ----------------------------
DROP TABLE IF EXISTS `permissions_base`;
CREATE TABLE `permissions_base` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `base_name` varchar(50) NOT NULL COMMENT '基地名称',
  `company_name` varchar(100) NOT NULL COMMENT '公司名称',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人真实姓名',
  PRIMARY KEY (`id`),
  KEY `idx_base_name` (`base_name`),
  KEY `idx_company_name` (`company_name`),
  KEY `idx_creator_name` (`creator_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='基地权限表';

-- ----------------------------
-- Records of permissions_base
-- ----------------------------
BEGIN;
INSERT INTO `permissions_base` (`id`, `base_name`, `company_name`, `created_at`, `creator_name`) VALUES (1, '北京基地', '北京科技有限公司', '2025-04-13 10:08:01', '张三');
INSERT INTO `permissions_base` (`id`, `base_name`, `company_name`, `created_at`, `creator_name`) VALUES (2, '上海基地', '上海信息有限公司', '2025-04-13 10:08:01', '李四');
INSERT INTO `permissions_base` (`id`, `base_name`, `company_name`, `created_at`, `creator_name`) VALUES (3, '广州基地', '广州电子有限公司', '2025-04-13 10:08:01', '王五');
INSERT INTO `permissions_base` (`id`, `base_name`, `company_name`, `created_at`, `creator_name`) VALUES (4, '深圳基地', '深圳创新有限公司', '2025-04-13 10:08:01', '赵六');
INSERT INTO `permissions_base` (`id`, `base_name`, `company_name`, `created_at`, `creator_name`) VALUES (5, '成都基地', '成都数据有限公司', '2025-04-13 10:08:01', '钱七');
COMMIT;

-- ----------------------------
-- Table structure for project_plans
-- ----------------------------
DROP TABLE IF EXISTS `project_plans`;
CREATE TABLE `project_plans` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '计划ID',
  `plan_date` date NOT NULL COMMENT '日期',
  `client_company` varchar(100) NOT NULL COMMENT '甲方公司名称',
  `base_name` varchar(50) NOT NULL COMMENT '基地名称',
  `project_id` varchar(50) NOT NULL COMMENT '项目ID',
  `project_name` varchar(100) NOT NULL COMMENT '项目名称',
  `project_unit` varchar(50) DEFAULT NULL COMMENT '项目单位',
  `issuer` varchar(50) DEFAULT NULL COMMENT '下发人',
  `senior_project_manager` varchar(50) DEFAULT NULL COMMENT '上级项目负责人',
  `project_leader` varchar(50) DEFAULT NULL COMMENT '项目组长',
  `undertaking_date` date DEFAULT NULL COMMENT '承接日期',
  `expected_end_date` date DEFAULT NULL COMMENT '预计完结日期',
  `planned_annotators` int DEFAULT NULL COMMENT '计划标注人数',
  `planned_reviewers` int DEFAULT NULL COMMENT '计划初审人数',
  `planned_annotation_output` int DEFAULT NULL COMMENT '计划标注产出(包/帧)',
  `planned_qa_output` int DEFAULT NULL COMMENT '计划质检产出(包/帧)',
  `actual_annotators` int DEFAULT NULL COMMENT '实际标注人数',
  `actual_reviewers` int DEFAULT NULL COMMENT '实际初审人数',
  `trained_persons` int DEFAULT NULL COMMENT '培训人数',
  `leave_persons` int DEFAULT NULL COMMENT '请假人数',
  `present_persons` int DEFAULT NULL COMMENT '实际出勤人数',
  `actual_annotation_output` int DEFAULT NULL COMMENT '实际标注产出(包/帧)',
  `actual_qa_output` int DEFAULT NULL COMMENT '实际质检产出(包/帧)',
  `annotation_difference` int DEFAULT NULL COMMENT '标注差额(实际-计划)',
  `qa_difference` int DEFAULT NULL COMMENT '质检差额(实际-计划)',
  `internal_project_manager` varchar(50) DEFAULT NULL COMMENT '项目内部负责人',
  `remarks` text COMMENT '备注',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `client_company` (`client_company`),
  KEY `base_name` (`base_name`),
  KEY `idx_plan_date` (`plan_date`),
  KEY `idx_project_leader` (`project_leader`),
  KEY `idx_internal_manager` (`internal_project_manager`),
  KEY `idx_completion_date` (`expected_end_date`),
  CONSTRAINT `project_plans_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects_all` (`project_id`),
  CONSTRAINT `project_plans_ibfk_2` FOREIGN KEY (`client_company`) REFERENCES `projects_all` (`client_company`),
  CONSTRAINT `project_plans_ibfk_3` FOREIGN KEY (`base_name`) REFERENCES `projects_all` (`base_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='项目计划执行表';

-- ----------------------------
-- Records of project_plans
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for project_units
-- ----------------------------
DROP TABLE IF EXISTS `project_units`;
CREATE TABLE `project_units` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '单位ID',
  `unit_type` varchar(20) NOT NULL COMMENT '单位类型(张/包/其他)',
  `custom_unit_name` varchar(20) DEFAULT NULL COMMENT '自定义单位名称(当单位类型为其他时填写)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator_real_name` varchar(50) NOT NULL COMMENT '创建者真实姓名',
  PRIMARY KEY (`id`),
  KEY `idx_unit_type` (`unit_type`),
  KEY `idx_creator` (`creator_real_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='项目单位表';

-- ----------------------------
-- Records of project_units
-- ----------------------------
BEGIN;
INSERT INTO `project_units` (`id`, `unit_type`, `custom_unit_name`, `created_at`, `creator_real_name`) VALUES (1, '张', NULL, '2025-04-13 12:28:47', '管理员');
INSERT INTO `project_units` (`id`, `unit_type`, `custom_unit_name`, `created_at`, `creator_real_name`) VALUES (2, '包', NULL, '2025-04-13 12:28:47', '管理员');
INSERT INTO `project_units` (`id`, `unit_type`, `custom_unit_name`, `created_at`, `creator_real_name`) VALUES (3, '帧', NULL, '2025-04-13 19:06:48', '管理员');
COMMIT;

-- ----------------------------
-- Table structure for projects_all
-- ----------------------------
DROP TABLE IF EXISTS `projects_all`;
CREATE TABLE `projects_all` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '项目ID',
  `project_date` date NOT NULL COMMENT '日期',
  `client_company` varchar(100) NOT NULL COMMENT '甲方公司名称',
  `project_id` varchar(50) NOT NULL COMMENT '项目ID',
  `project_name` varchar(100) NOT NULL COMMENT '项目名称',
  `project_unit` varchar(50) DEFAULT NULL COMMENT '项目单位',
  `quote_unit` varchar(50) DEFAULT NULL COMMENT '报价单位',
  `project_manager` varchar(50) NOT NULL COMMENT '项目经理总负责',
  `issuer` varchar(50) DEFAULT NULL COMMENT '下发人',
  `senior_project_manager` varchar(50) DEFAULT NULL COMMENT '上级项目负责人',
  `base_name` varchar(50) DEFAULT NULL COMMENT '基地名称',
  `base_unit_price` decimal(12,2) DEFAULT NULL COMMENT '基地单价',
  `base_quote_efficiency` decimal(5,2) DEFAULT NULL COMMENT '基地报价效率(%)',
  `base_tax_rate` decimal(5,2) DEFAULT NULL COMMENT '基地税率(%)',
  `base_project_manager` varchar(50) DEFAULT NULL COMMENT '基地项目负责人',
  `undertaking_unit_price` decimal(12,2) DEFAULT NULL COMMENT '承接单价',
  `quote_efficiency` decimal(5,2) DEFAULT NULL COMMENT '报价效率(%)',
  `pr_price` decimal(12,2) DEFAULT NULL COMMENT '公关价',
  `actual_unit_price` decimal(12,2) DEFAULT NULL COMMENT '到手实际单价',
  `undertaking_tax_rate` decimal(5,2) DEFAULT NULL COMMENT '承接税率(%)',
  `undertaking_date` date DEFAULT NULL COMMENT '承接日期',
  `expected_completion_date` date DEFAULT NULL COMMENT '预计完结日期',
  `billing_date` date DEFAULT NULL COMMENT '账单日期',
  `client_payment_date` date DEFAULT NULL COMMENT '甲方打款日期',
  `is_invoiced_to_client` tinyint(1) DEFAULT '0' COMMENT '是否与客户开票(0-否,1-是)',
  `is_completed` tinyint(1) DEFAULT '0' COMMENT '是否完结(0-否,1-是)',
  `salary_payment_date` date DEFAULT NULL COMMENT '工资发放日期',
  `supplier_payment_date` date DEFAULT NULL COMMENT '供应商打款日期',
  `is_supplier_invoiced` tinyint(1) DEFAULT '0' COMMENT '供应商是否已开票(0-否,1-是)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_project_id` (`project_id`) COMMENT '项目ID唯一索引',
  KEY `idx_client_company` (`client_company`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_project_name` (`project_name`),
  KEY `idx_project_manager` (`project_manager`),
  KEY `idx_base_name` (`base_name`),
  KEY `idx_undertaking_date` (`undertaking_date`),
  KEY `idx_expected_completion_date` (`expected_completion_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='项目信息表';

-- ----------------------------
-- Records of projects_all
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for projects_quote_units
-- ----------------------------
DROP TABLE IF EXISTS `projects_quote_units`;
CREATE TABLE `projects_quote_units` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '报价单位ID',
  `unit_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '报价单位类型',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unit_type` (`unit_type`) COMMENT '单位类型唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='报价单位类型表';

-- ----------------------------
-- Records of projects_quote_units
-- ----------------------------
BEGIN;
INSERT INTO `projects_quote_units` (`id`, `unit_type`, `created_at`) VALUES (1, '框', '2025-04-13 12:30:53');
INSERT INTO `projects_quote_units` (`id`, `unit_type`, `created_at`) VALUES (2, '线', '2025-04-13 12:30:53');
INSERT INTO `projects_quote_units` (`id`, `unit_type`, `created_at`) VALUES (3, '张', '2025-04-13 12:30:53');
INSERT INTO `projects_quote_units` (`id`, `unit_type`, `created_at`) VALUES (4, '组', '2025-04-13 12:30:53');
INSERT INTO `projects_quote_units` (`id`, `unit_type`, `created_at`) VALUES (5, '多边形', '2025-04-13 12:30:53');
INSERT INTO `projects_quote_units` (`id`, `unit_type`, `created_at`) VALUES (6, '区域', '2025-04-13 19:09:55');
COMMIT;

-- ----------------------------
-- Table structure for projects_tax_rates
-- ----------------------------
DROP TABLE IF EXISTS `projects_tax_rates`;
CREATE TABLE `projects_tax_rates` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '税率ID',
  `month_year` char(7) NOT NULL COMMENT '月份(格式:YYYY-MM)',
  `client_company` varchar(100) NOT NULL COMMENT '甲方公司名称',
  `client_tax_rate` decimal(5,2) NOT NULL COMMENT '甲方公司税率(%)',
  `undertaking_tax_rate` decimal(5,2) NOT NULL COMMENT '承接税率(%)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_client_month` (`client_company`,`month_year`) COMMENT '同一公司同月税率唯一',
  KEY `idx_month_year` (`month_year`),
  KEY `idx_client_company` (`client_company`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='税率表';

-- ----------------------------
-- Records of projects_tax_rates
-- ----------------------------
BEGIN;
INSERT INTO `projects_tax_rates` (`id`, `month_year`, `client_company`, `client_tax_rate`, `undertaking_tax_rate`, `created_at`) VALUES (1, '2023-01', 'ABC科技有限公司', 6.00, 3.00, '2025-04-13 12:19:10');
INSERT INTO `projects_tax_rates` (`id`, `month_year`, `client_company`, `client_tax_rate`, `undertaking_tax_rate`, `created_at`) VALUES (2, '2023-01', 'XYZ制造有限公司', 9.00, 5.00, '2025-04-13 12:19:10');
INSERT INTO `projects_tax_rates` (`id`, `month_year`, `client_company`, `client_tax_rate`, `undertaking_tax_rate`, `created_at`) VALUES (3, '2023-02', 'ABC科技有限公司', 6.00, 3.00, '2025-04-13 12:19:10');
INSERT INTO `projects_tax_rates` (`id`, `month_year`, `client_company`, `client_tax_rate`, `undertaking_tax_rate`, `created_at`) VALUES (4, '2023-02', 'DEF建筑集团', 13.00, 6.00, '2025-04-13 12:19:10');
COMMIT;

-- ----------------------------
-- Table structure for scheduling_projectcompany_nickname
-- ----------------------------
DROP TABLE IF EXISTS `scheduling_projectcompany_nickname`;
CREATE TABLE `scheduling_projectcompany_nickname` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '组织',
  `realName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '真实姓名',
  `projectCompany` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '项目公司',
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '项目公司的用户 昵称',
  `createTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of scheduling_projectcompany_nickname
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for select_projectcompany
-- ----------------------------
DROP TABLE IF EXISTS `select_projectcompany`;
CREATE TABLE `select_projectcompany` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `finallyCompany` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `finallyCompanyRealName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `state` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'true',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of select_projectcompany
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sys_bases
-- ----------------------------
DROP TABLE IF EXISTS `sys_bases`;
CREATE TABLE `sys_bases` (
  `id` int NOT NULL AUTO_INCREMENT,
  `base_name` varchar(50) NOT NULL COMMENT '基地名称',
  `location` varchar(100) NOT NULL COMMENT '基地所在地',
  `short_name` varchar(20) DEFAULT NULL COMMENT '简称',
  `permissions` text COMMENT '权限配置(JSON格式)',
  `management_permission` tinyint(1) DEFAULT '0' COMMENT '管理权限(1:有,0:无)',
  `manager` varchar(50) DEFAULT NULL COMMENT '基地负责人',
  `default_tax_rate` decimal(5,2) DEFAULT '0.00' COMMENT '默认税率(%)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_base_name` (`base_name`) COMMENT '基地名称唯一',
  KEY `idx_base_name` (`base_name`),
  KEY `idx_location` (`location`),
  KEY `idx_short_name` (`short_name`),
  KEY `idx_manager` (`manager`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='基地信息表';

-- ----------------------------
-- Records of sys_bases
-- ----------------------------
BEGIN;
INSERT INTO `sys_bases` (`id`, `base_name`, `location`, `short_name`, `permissions`, `management_permission`, `manager`, `default_tax_rate`, `created_at`, `updated_at`) VALUES (1, '北京总部基地', '北京市朝阳区建国路88号', '京基地', '{\"finance\":1,\"hr\":1,\"inventory\":1}', 1, '张建国', 6.00, '2025-04-12 18:19:08', '2025-04-12 18:19:08');
INSERT INTO `sys_bases` (`id`, `base_name`, `location`, `short_name`, `permissions`, `management_permission`, `manager`, `default_tax_rate`, `created_at`, `updated_at`) VALUES (2, '上海研发中心', '上海市浦东新区张江高科技园区', '沪研发', '{\"rd\":1,\"testing\":1}', 0, '李科技', 3.00, '2025-04-12 18:19:08', '2025-04-12 18:19:08');
INSERT INTO `sys_bases` (`id`, `base_name`, `location`, `short_name`, `permissions`, `management_permission`, `manager`, `default_tax_rate`, `created_at`, `updated_at`) VALUES (3, '广州物流中心', '广州市白云区机场路', '穗物流', '{\"logistics\":1,\"warehouse\":1}', 0, '王运输', 5.50, '2025-04-12 18:19:08', '2025-04-12 18:19:08');
INSERT INTO `sys_bases` (`id`, `base_name`, `location`, `short_name`, `permissions`, `management_permission`, `manager`, `default_tax_rate`, `created_at`, `updated_at`) VALUES (4, '深圳生产基地', '深圳市龙岗区工业园', '深生产', '{\"production\":1,\"quality\":1}', 1, '陈制造', 0.00, '2025-04-12 18:19:08', '2025-04-12 18:19:08');
INSERT INTO `sys_bases` (`id`, `base_name`, `location`, `short_name`, `permissions`, `management_permission`, `manager`, `default_tax_rate`, `created_at`, `updated_at`) VALUES (5, '成都客服中心', '成都市高新区天府软件园', '蓉客服', '{\"customer\":1,\"support\":1}', 0, '刘服务', 13.00, '2025-04-12 18:19:08', '2025-04-12 18:19:08');
COMMIT;

-- ----------------------------
-- Table structure for sys_emergency_contact_relations
-- ----------------------------
DROP TABLE IF EXISTS `sys_emergency_contact_relations`;
CREATE TABLE `sys_emergency_contact_relations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `relation_name` varchar(20) NOT NULL COMMENT '关系名称',
  `relation_code` varchar(10) DEFAULT NULL COMMENT '关系代码(可选)',
  `is_family` tinyint(1) DEFAULT '1' COMMENT '是否家庭成员(1:是,0:否)',
  `sort_order` int DEFAULT '0' COMMENT '排序序号',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `relation_code` (`relation_code`),
  KEY `idx_relation_name` (`relation_name`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='紧急联系人关系表';

-- ----------------------------
-- Records of sys_emergency_contact_relations
-- ----------------------------
BEGIN;
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (1, '父亲', 'father', 1, 1, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (2, '母亲', 'mother', 1, 2, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (3, '爱人', 'spouse', 1, 3, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (4, '哥哥', 'elder_bro', 1, 4, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (5, '弟弟', 'young_bro', 1, 5, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (6, '姐姐', 'elder_sis', 1, 6, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (7, '妹妹', 'young_sis', 1, 7, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (8, '爷爷', 'grandpa', 1, 8, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (9, '奶奶', 'grandma', 1, 9, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
INSERT INTO `sys_emergency_contact_relations` (`id`, `relation_name`, `relation_code`, `is_family`, `sort_order`, `created_at`, `updated_at`) VALUES (10, '其他', 'other', 0, 10, '2025-04-12 18:34:46', '2025-04-12 18:34:46');
COMMIT;

-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `parent_id` bigint DEFAULT NULL COMMENT '父菜单ID',
  `menu_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '菜单名称',
  `path` varchar(200) DEFAULT NULL COMMENT '路由路径',
  `component` varchar(200) DEFAULT NULL COMMENT '组件路径',
  `icon` varchar(50) DEFAULT NULL COMMENT '图标',
  `sort` int DEFAULT '0' COMMENT '排序',
  `visible` tinyint(1) DEFAULT '1' COMMENT '是否可见(0:不可见,1:可见)',
  `redirect` varchar(200) DEFAULT NULL COMMENT '重定向路径',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='菜单';

-- ----------------------------
-- Records of sys_menu
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '角色名称',
  `code` varchar(50) NOT NULL COMMENT '角色编码',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色';

-- ----------------------------
-- Records of sys_role
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sys_role_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` (
  `role_id` bigint NOT NULL COMMENT '角色ID',
  `menu_id` bigint NOT NULL COMMENT '菜单ID',
  PRIMARY KEY (`role_id`,`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色菜单关联';

-- ----------------------------
-- Records of sys_role_menu
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `role_id` bigint NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`user_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户角色关联';

-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for system_menu_permissions
-- ----------------------------
DROP TABLE IF EXISTS `system_menu_permissions`;
CREATE TABLE `system_menu_permissions` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `base_type` enum('内部','外部公司') NOT NULL COMMENT '基地类型',
  `base_name` varchar(50) NOT NULL COMMENT '基地名称',
  `user_permission` enum('管理员','项目经理','普通用户','访客') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户权限等级',
  `route_path` varchar(100) NOT NULL COMMENT '路由路径',
  `menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
  `parent_menu_id` int DEFAULT NULL COMMENT '父菜单ID(用于多级菜单)',
  `menu_icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',
  `sort_order` int DEFAULT '0' COMMENT '菜单排序',
  `is_visible` tinyint(1) DEFAULT '1' COMMENT '是否可见:1-是,0-否',
  `creator_name` varchar(50) NOT NULL COMMENT '添加者姓名',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_menu_permission` (`base_type`,`base_name`,`user_permission`,`route_path`) COMMENT '唯一约束',
  KEY `idx_base_type` (`base_type`),
  KEY `idx_base_name` (`base_name`),
  KEY `idx_user_permission` (`user_permission`),
  KEY `idx_route_path` (`route_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统菜单权限表';

-- ----------------------------
-- Records of system_menu_permissions
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for user_accounts
-- ----------------------------
DROP TABLE IF EXISTS `user_accounts`;
CREATE TABLE `user_accounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `base_type` enum('内部','外部公司') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '基地类型',
  `base_name` varchar(50) DEFAULT NULL COMMENT '基地名称',
  `yearmonth` int(6) unsigned zerofill DEFAULT NULL COMMENT '年月(6位数字，如202512)',
  `real_name` varchar(50) DEFAULT NULL COMMENT '用户姓名',
  `account_name` varchar(50) NOT NULL COMMENT '账号名',
  `creator_name` varchar(50) DEFAULT NULL COMMENT '创建者姓名',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_type` (`base_type`),
  KEY `idx_base_name` (`base_name`),
  KEY `idx_yearmonth` (`yearmonth`),
  KEY `idx_real_name` (`real_name`),
  KEY `idx_account_name` (`account_name`),
  KEY `idx_creator_name` (`creator_name`),
  CONSTRAINT `chk_yearmonth` CHECK (((`yearmonth` >= 100001) and (`yearmonth` <= 999912) and ((`yearmonth` % 100) between 1 and 12)))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户使用账号表';

-- ----------------------------
-- Records of user_accounts
-- ----------------------------
BEGIN;
INSERT INTO `user_accounts` (`id`, `base_type`, `base_name`, `yearmonth`, `real_name`, `account_name`, `creator_name`, `created_at`, `updated_at`) VALUES (1, '内部', '北京基地', 202301, '张三', 'zhangsan_work', '管理员李', '2025-04-13 09:57:48', '2025-04-13 09:57:48');
INSERT INTO `user_accounts` (`id`, `base_type`, `base_name`, `yearmonth`, `real_name`, `account_name`, `creator_name`, `created_at`, `updated_at`) VALUES (2, '内部', '北京基地', 202302, '张三', 'zhangsan_personal', '系统王', '2025-04-13 09:57:48', '2025-04-13 09:57:48');
INSERT INTO `user_accounts` (`id`, `base_type`, `base_name`, `yearmonth`, `real_name`, `account_name`, `creator_name`, `created_at`, `updated_at`) VALUES (3, '外部公司', '上海基地', 202312, '李四', 'lisi_vendor', '操作员赵', '2025-04-13 09:57:48', '2025-04-13 09:57:48');
INSERT INTO `user_accounts` (`id`, `base_type`, `base_name`, `yearmonth`, `real_name`, `account_name`, `creator_name`, `created_at`, `updated_at`) VALUES (4, '内部', '广州基地', 202405, '王五', 'wangwu_work', '主管钱', '2025-04-13 09:57:48', '2025-04-13 09:57:48');
INSERT INTO `user_accounts` (`id`, `base_type`, `base_name`, `yearmonth`, `real_name`, `account_name`, `creator_name`, `created_at`, `updated_at`) VALUES (5, '外部公司', '深圳基地', 202406, '赵六', 'zhaoliu_client', '经理孙', '2025-04-13 09:57:48', '2025-04-13 09:57:48');
COMMIT;

-- ----------------------------
-- Table structure for user_message
-- ----------------------------
DROP TABLE IF EXISTS `user_message`;
CREATE TABLE `user_message` (
  `id` int NOT NULL AUTO_INCREMENT,
  `base_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '基地名称',
  `phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '电话',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `real_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '真实姓名',
  `gender` enum('男','女') DEFAULT NULL COMMENT '性别',
  `id_card` varchar(18) DEFAULT NULL COMMENT '身份证号',
  `address` varchar(200) DEFAULT NULL COMMENT '家庭地址',
  `emergency_contact` varchar(50) DEFAULT NULL COMMENT '紧急联系人',
  `emergency_relation` varchar(20) DEFAULT NULL COMMENT '紧急联系人关系',
  `bank_account_name` varchar(50) DEFAULT NULL COMMENT '银行卡用户名',
  `bank_account_number` varchar(30) DEFAULT NULL COMMENT '银行卡号',
  `bank_name` varchar(50) DEFAULT NULL COMMENT '所属银行',
  `hire_date` date DEFAULT NULL COMMENT '入职日期',
  `leave_date` date DEFAULT NULL COMMENT '离职日期',
  `submit_date` date DEFAULT NULL COMMENT '提交日期',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否在职(1:在职,0:离职)',
  `group_name` varchar(50) DEFAULT NULL COMMENT '分组',
  `management_permission` tinyint(1) DEFAULT '0' COMMENT '管理权限(1:有,0:无)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `base_type` varchar(255) NOT NULL COMMENT '基地类型（本部公司，外部公司）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_card` (`id_card`),
  KEY `idx_id` (`id`),
  KEY `idx_phone` (`phone_number`),
  KEY `idx_name` (`real_name`),
  KEY `idx_id_card` (`id_card`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_group_name` (`group_name`),
  KEY `idx_hire_date` (`hire_date`)
) ENGINE=InnoDB AUTO_INCREMENT=709 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';

-- ----------------------------
-- Records of user_message
-- ----------------------------
BEGIN;
INSERT INTO `user_message` (`id`, `base_name`, `phone_number`, `password`, `real_name`, `gender`, `id_card`, `address`, `emergency_contact`, `emergency_relation`, `bank_account_name`, `bank_account_number`, `bank_name`, `hire_date`, `leave_date`, `submit_date`, `is_active`, `group_name`, `management_permission`, `created_at`, `updated_at`, `base_type`) VALUES (704, '北京基地', '13800138001', 'e10adc3949ba59abbe56e057f20f883e', '张三', '男', '110101199001011234', '北京市朝阳区建国路88号', '李四', '配偶', '张三', '6222020100001234567', '工商银行', '2020-01-15', NULL, '2020-01-10', 1, '技术部', 1, '2025-04-12 18:24:53', '2025-04-12 18:24:53', '');
INSERT INTO `user_message` (`id`, `base_name`, `phone_number`, `password`, `real_name`, `gender`, `id_card`, `address`, `emergency_contact`, `emergency_relation`, `bank_account_name`, `bank_account_number`, `bank_name`, `hire_date`, `leave_date`, `submit_date`, `is_active`, `group_name`, `management_permission`, `created_at`, `updated_at`, `base_type`) VALUES (705, '上海基地', '13900139002', 'e10adc3949ba59abbe56e057f20f883e', '王芳', '女', '310101199102022345', '上海市浦东新区张江高科技园区', '王强', '父亲', '王芳', '6228480400002345678', '农业银行', '2019-05-20', NULL, '2019-05-15', 1, '市场部', 0, '2025-04-12 18:24:53', '2025-04-12 18:24:53', '');
INSERT INTO `user_message` (`id`, `base_name`, `phone_number`, `password`, `real_name`, `gender`, `id_card`, `address`, `emergency_contact`, `emergency_relation`, `bank_account_name`, `bank_account_number`, `bank_name`, `hire_date`, `leave_date`, `submit_date`, `is_active`, `group_name`, `management_permission`, `created_at`, `updated_at`, `base_type`) VALUES (706, '广州基地', '13700137003', 'e10adc3949ba59abbe56e057f20f883e', '李伟', '男', '440101198803033456', '广州市天河区体育西路', '张丽', '配偶', '李伟', '6227007200003456789', '建设银行', '2021-03-10', '2022-12-31', '2021-03-05', 0, '销售部', 0, '2025-04-12 18:24:53', '2025-04-12 18:24:53', '');
INSERT INTO `user_message` (`id`, `base_name`, `phone_number`, `password`, `real_name`, `gender`, `id_card`, `address`, `emergency_contact`, `emergency_relation`, `bank_account_name`, `bank_account_number`, `bank_name`, `hire_date`, `leave_date`, `submit_date`, `is_active`, `group_name`, `management_permission`, `created_at`, `updated_at`, `base_type`) VALUES (707, '深圳基地', '13600136004', 'e10adc3949ba59abbe56e057f20f883e', '陈静', '女', '440301199504044567', '深圳市南山区科技园', '陈明', '兄弟', '陈静', '6226090000004567890', '招商银行', '2022-02-18', NULL, '2022-02-15', 1, '人事部', 1, '2025-04-12 18:24:53', '2025-04-12 18:24:53', '');
INSERT INTO `user_message` (`id`, `base_name`, `phone_number`, `password`, `real_name`, `gender`, `id_card`, `address`, `emergency_contact`, `emergency_relation`, `bank_account_name`, `bank_account_number`, `bank_name`, `hire_date`, `leave_date`, `submit_date`, `is_active`, `group_name`, `management_permission`, `created_at`, `updated_at`, `base_type`) VALUES (708, '成都基地', '13500135005', 'e10adc3949ba59abbe56e057f20f883e', '刘强', '男', '510101198706055678', '成都市武侯区人民南路', '刘芳', '姐妹', '刘强', '6225750000005678901', '交通银行', '2018-11-05', NULL, '2018-11-01', 1, '财务部', 1, '2025-04-12 18:24:53', '2025-04-12 18:24:53', '');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
